package com.hnqcgczy.supplier.dao.impl;

import com.hnqcgczy.supplier.dao.BankDao;
import com.hnqcgczy.supplier.db.MyTransaction;
import com.hnqcgczy.supplier.utils.BaseDao;
import com.hnqcgczy.supplier.vo.Bank;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class BankDaoImpl extends BaseDao implements BankDao {
    public BankDaoImpl(MyTransaction myTransaction) {
        super(myTransaction);
    }

    @Override
    public int add(Bank bank) throws SQLException {
        //1.准备sql
        String sql = "INSERT INTO `t_bank`(`bank_id`, `company_id`, `bank_deposit`, `sub_branch`, `province`, `city`, `account_name`, `bank_account`) VALUES (null, ?,?,?,?,?,?,?)";
        //2.调用公共方法update
        int i = update(sql, bank.getCompanyId(), bank.getBankDeposit(), bank.getSubBranch(), bank.getProvince(), bank.getCity(), bank.getAccountName(), bank.getBankAccount());
        //3.关闭资源
        closeResource();
        return i;
    }

    @Override
    public int update(Bank bank) throws SQLException {
        //1.准备sql
        String sql = "UPDATE `t_bank` SET `company_id` = ?, `bank_deposit` = ?, `sub_branch` = ?, `province` = ?, `city` = ?, `account_name` = ?, `bank_account` = ? WHERE `bank_id` = ?";
        //2.调用公共方法update
        int i = update(sql, bank.getBankId(), bank.getBankDeposit(), bank.getSubBranch(), bank.getProvince(), bank.getCity(), bank.getAccountName(), bank.getBankAccount(), bank.getBankId());

        //3.关闭资源
        closeResource();
        return i;
    }

    @Override
    public int delete(Integer bankId) throws SQLException {
        //1.准备sql
        String sql = "delete from `t_bank` where `bank_id` = ?";
        //2.调用公共方法update
        int i = update(sql, bankId);
        //3.关闭资源
        closeResource();
        return i;
    }

    @Override
    public Bank findOne(Integer bankId) throws SQLException {
        //1.准备sql
        String sql = "select bank_id,company_id,bank_deposit,sub_branch,province,city,account_name,bank_account from `t_bank` where `bank_id` = ?";
        //2.调用公共方法update
        ResultSet rs = query(sql, bankId);
        Bank bank = null;
        if (rs.next()) {

            while (rs.next()) {
                bank = new Bank();
                //从数据库中拿出来填充到实体
                bank.setBankId(rs.getInt("bank_id"));
                bank.setCompanyId(rs.getInt("company_id"));
                bank.setBankDeposit(rs.getString("bank_deposit"));
                bank.setSubBranch(rs.getString("sub_branch"));
                bank.setProvince(rs.getString("province"));
                bank.setCity(rs.getString("city"));
                bank.setAccountName(rs.getString("account_name"));
                bank.setBankAccount(rs.getString("bank_account"));

            }
        }
        //3.关闭资源
        closeResource();
        return bank;
    }

    @Override
    public List<Bank> findByLike(Bank bank, int start, int size) throws SQLException {
        StringBuffer msg = new StringBuffer("select bank_id,company_id,bank_deposit,sub_branch,province,city,account_name,bank_account from `t_bank` where  1=1 ");
        if (bank != null) {
            //bank_deposit开户行
            if (bank.getBankDeposit() != null && !"".equals(bank.getBankDeposit())) {
                msg.append(" and bank_deposit like '%").append(bank.getBankDeposit()).append("%'");
            }
            //bank_account
            if (bank.getBankAccount() != null && !"".equals(bank.getBankAccount())) {
                msg.append(" and bank_account like '%").append(bank.getBankAccount()).append("%'");
            }
        }
        //分页
        msg.append(" limit ").append(start).append(",").append(size);
        String sql = msg.toString();
        System.out.println(sql);
        List<Bank> lists = null;
        ResultSet rs = query(sql);

        if (rs != null) {
            lists = new ArrayList<>();
            while (rs.next()) {
                Bank temp = new Bank();
                //从数据库中拿出来填充到实体
                temp.setBankId(rs.getInt("bank_id"));
                temp.setCompanyId(rs.getInt("company_id"));
                temp.setBankDeposit(rs.getString("bank_deposit"));
                temp.setSubBranch(rs.getString("sub_branch"));
                temp.setProvince(rs.getString("province"));
                temp.setCity(rs.getString("city"));
                temp.setAccountName(rs.getString("account_name"));
                temp.setBankAccount(rs.getString("bank_account"));
                lists.add(temp);
                temp = null;
            }
        }
        //3.关闭资源
        closeResource();


        return lists;
    }

    @Override
    public int totals(Bank bank) throws SQLException {
        StringBuffer msg = new StringBuffer("select count(*) totals from `t_bank` where  1=1 ");
        if (bank != null) {
            //bank_deposit开户行
            if (bank.getBankDeposit() != null && !"".equals(bank.getBankDeposit())) {
                msg.append(" and bank_deposit like '%").append(bank.getBankDeposit()).append("%'");
            }
            //bank_account
            if (bank.getBankAccount() != null && !"".equals(bank.getBankAccount())) {
                msg.append(" and bank_account like '%").append(bank.getBankAccount()).append("%'");
            }
        }

        String sql = msg.toString();
        ResultSet rs = query(sql);
        int totals = 0;
        if (rs != null) {
            while (rs.next()) {
                totals = rs.getInt("totals");
            }
        }
        return totals;
    }
}
